package bsr.basara.autodata.service.impl;

import bsr.basara.autodata.dao.ColumnConfigRepository;
import bsr.basara.autodata.dao.ColumnInfoRepository;
import bsr.basara.autodata.dao.TableConfigRepository;
import bsr.basara.autodata.dao.TableInfoRepository;
import bsr.basara.autodata.entity.ColumnConfig;
import bsr.basara.autodata.entity.ColumnInfo;
import bsr.basara.autodata.entity.TableConfig;
import bsr.basara.autodata.entity.TableInfo;
import bsr.basara.autodata.util.*;
import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 自动数据服务
 *
 * @author basara
 */
@Service
public class AutoDataService {

    @Resource
    private TableInfoRepository tableInfoRepository;

    @Resource
    private ColumnInfoRepository columnInfoRepository;

    @Resource
    private TableConfigRepository tableConfigRepository;

    @Resource
    private ColumnConfigRepository columnConfigRepository;

    @PersistenceContext
    private EntityManager entityManager;

    private static final Pattern NAME_NUM = Pattern.compile("(\\w+)(\\d+)");

    /**
     * 自动获取表与列定义
     * 表名逗号分隔 空值则扫描加载所有表
     *
     * @param devTables 设备表
     * @param hisTables 历史表
     */
    public void autoTableAndCol(String devTables, String hisTables) {
        List<String> devTableNames = null;
        List<String> hisTableNames = null;
        if (StrUtil.isNotEmpty(devTables)) {
            devTableNames = Arrays.asList(devTables.split(","));
        }
        if (StrUtil.isNotEmpty(hisTables)) {
            hisTableNames = Arrays.asList(hisTables.split(","));
        }
        autoTable(devTableNames, hisTableNames);
    }

    /**
     * 历史数据生成
     *
     * @param tableInfo 表
     */
    public void autoData(TableInfo tableInfo) {

        List<TableConfig> tableConfigs = tableConfigRepository.findByDataTable(tableInfo.getName());
        if (tableConfigs == null || tableConfigs.size() < 1) {
            return;
        }
        List<String> collect = tableConfigs.stream().filter(c -> c.getDevId() != null).map(c -> String.valueOf(c.getDevId())).collect(Collectors.toList());
        String join = String.join(",", collect);
        String tj = "";
        if (StrUtil.isNotEmpty(join)) {
            tj = " where src_id not in (" + join + ")";
        }
        //历史数据通用配置是否执行过
        boolean hisAll = false;
        for (TableConfig tableConfig : tableConfigs) {
            List<ColumnConfig> columnConfigs = columnConfigRepository.findAllByTableConfigId(tableConfig.getId());

            //设备表
            String devTable = tableConfig.getDevTable();
            //数据表
            String dataTable = tableConfig.getDataTable();

            Long devId = tableConfig.getDevId();

            Date lastTime = tableConfig.getLastTime();

            //设备数据条数（数据时间间隔）
            int timeSpace = tableConfig.getTimeSpace();

            List<Record> insertData = new ArrayList<>();

            //设备表数据生成
            if (dataTable.equals(devTable) && lastTime == null) {
                devData(insertData, columnConfigs, timeSpace);
                //入库
                Db.batchSave(dataTable, insertData, 1000);

                tableConfig.setUpdateTime(new Date());
                tableConfig.setLastTime(tableConfig.getUpdateTime());
                //更新配置
                tableConfigRepository.save(tableConfig);
                break;
            } else if (!dataTable.equals(devTable)) {
                Date now = DataUtils.hisTime(new Date(), timeSpace);
                //数据表数据生成
                TableInfo devTableInfo = tableInfoRepository.findByName(devTable);
                List<ColumnInfo> devCols = columnInfoRepository.findByTypeAndTableId(2, devTableInfo.getId());
                List<ColumnInfo> dataCols = columnInfoRepository.findByTypeAndTableId(2, tableInfo.getId());
                if (lastTime == null) {
                    lastTime = DateUtil.parse("2022-03-08 16:30:00");
                }
                if (devId == null) {
                    if (hisAll) {
                        continue;
                    }
                    //设备列表
                    List<Record> devs = Db.find("select * from " + devTable + tj);
                    for (Record dev : devs) {
                        //数据冗余
                        Map<String, Object> redundant = redundant(dev, devCols, dataCols);
                        devHis(insertData, redundant, dev, columnConfigs, lastTime, now, timeSpace);
                    }
                    hisAll = true;
                } else {
                    Record dev = Db.findFirst("select * from " + devTable + " where src_id=?", devId);
                    if (dev != null) {
                        Map<String, Object> redundant = redundant(dev, devCols, dataCols);
                        devHis(insertData, redundant, dev, columnConfigs, lastTime, now, timeSpace);
                    }
                }
                //入库
                Db.batchSave(dataTable, insertData, 1000);
                tableConfig.setLastTime(now);
                tableConfig.setUpdateTime(new Date());
                //更新配置
                tableConfigRepository.save(tableConfig);
            }

        }

    }

    /**
     * 数据冗余补齐更新
     *
     * @param dataTableInfo 数据表
     * @param begin         开始日期
     * @param end           结束日期
     */
    public void autoRedundant(TableInfo dataTableInfo, Date begin, Date end) {
        //更新列表
        List<Record> updateList;

        String dataTable = dataTableInfo.getName();
        TableInfo devTableInfo = tableInfoRepository.findById(dataTableInfo.getDevTableId()).orElse(null);
        if (devTableInfo == null) {
            return;
        }

        //设备列
        List<ColumnInfo> devCols = columnInfoRepository.findByTypeAndTableId(2, devTableInfo.getId());
        //数据列
        List<ColumnInfo> dataCols = columnInfoRepository.findByTypeAndTableId(2, dataTableInfo.getId());

        //设备表的设备
        List<Record> devs = Db.find("select * from " + devTableInfo.getName());

        updateList = new ArrayList<>();
        for (Record dev : devs) {
            //冗余字段
            Map<String, Object> redundant = redundant(dev, devCols, dataCols);
            if (redundant.size() > 0) {
                //设备历史数据
                List<Record> datas = Db.find("select * from " + dataTable + " where dev_id=? and time>=? and time<?", dev.getLong("src_id"), begin, end);
                for (Record data : datas) {
                    data.setColumns(redundant);
                }
                updateList.addAll(datas);
                if (updateList.size() > 1000) {
                    Db.batchUpdate(dataTable, updateList, 1000);
                    updateList.clear();
                }
            }
        }
        if (updateList.size() > 0) {
            Db.batchUpdate(dataTable, updateList, 1000);
        }

    }

    /**
     * 采集点配置数据粗步生成
     *
     * @param devTables 设备表
     * @param hisTables 历史表
     */
    public void autoSyncPoint(String devTables, String hisTables) {
        List<String> devTableNames = null;
        List<String> hisTableNames = null;
        if (StrUtil.isNotEmpty(devTables)) {
            devTableNames = Arrays.asList(devTables.split(","));
        }
        if (StrUtil.isNotEmpty(hisTables)) {
            hisTableNames = Arrays.asList(hisTables.split(","));
        }
        syncPoint(devTableNames, hisTableNames, 25L, 1L, 1);
    }


    /**
     * 冗余及默认字段
     *
     * @param dev      设备信息
     * @param devCols  设备列
     * @param dataCols 数据列
     * @return
     */
    private Map<String, Object> redundant(Record dev, List<ColumnInfo> devCols, List<ColumnInfo> dataCols) {
        Map<String, Object> map = new HashMap<>();
        //设备名称冗余
        ColumnInfo nameCol = new ColumnInfo();
        nameCol.setName("name");
        devCols.add(nameCol);
        for (ColumnInfo devCol : devCols) {
            String devColName = devCol.getName();
            devColName = devColName.toLowerCase();
            Object value = dev.get(devColName);
            if (value != null) {
                for (ColumnInfo dataCol : dataCols) {
                    String dataColName = dataCol.getName();
                    dataColName = dataColName.toLowerCase();
                    if (devColName.equalsIgnoreCase(dataColName)) {
                        map.put(devColName, value);
                        break;
                    }
                    if (dataColName.equalsIgnoreCase("dev_" + devColName)) {
                        map.put(dataColName, value);
                        break;
                    }
                }
            }
        }
        return map;
    }

    /**
     * 设备历史数据
     *
     * @param insertData    生成数据列表
     * @param columnConfigs 字段生成规则配置
     * @param timeSpace     数据个数
     */
    private void devData(List<Record> insertData, List<ColumnConfig> columnConfigs, int timeSpace) {

        for (int i = 0; i < timeSpace; i++) {
            Record dev = new Record();

            for (ColumnConfig columnConfig : columnConfigs) {

                String columnName = columnConfig.getColumn();
                //0固定值，1基数波动，2上下限值，3一定趋势，4其他值计算，5分片生成
                int type = columnConfig.getType();
                //0字符串 1整数 2浮点数 3时间 99其他
                int dataType = columnConfig.getDataType();

                Matcher matcher = NAME_NUM.matcher(columnName);
                int columnNum = 1;
                String columnPre = columnName;
                if (matcher.matches()) {
                    columnPre = matcher.group(1);
                    columnNum = Integer.parseInt(matcher.group(2));
                }
                if (columnNum > 15) {
                    for (int j = 0; j < columnNum; j++) {
                        columnName = columnPre + (j + 1);
                        devData(columnNum, j, dev, columnConfig, columnName, type, dataType);
                    }
                } else {
                    devData(timeSpace, i, dev, columnConfig, columnName, type, dataType);
                }
            }
            insertData.add(dev);
        }

    }

    /**
     * 设备数据生成
     *
     * @param size       数据个数
     * @param i          当前数据位置
     * @param dev        设备数据
     * @param column     配置
     * @param columnName 字段名称
     * @param type       生成方式
     * @param dataType   字段类型
     */
    private void devData(int size, int i, Record dev, ColumnConfig column, String columnName, int type, int dataType) {
        if (dataType == 0) {
            dev.set(columnName, ChineseCharUtils.randomChineseChars(2, 10));
        } else if (dataType == 3) {
            dev.set(columnName, RandomUtil.randomDate(new Date(), DateField.MINUTE, -10, 10));
        } else {
            switch (type) {
                case 0:
                    dev.set(columnName, column.getBase());
                    break;
                case 1:
                    dev.set(columnName, DataUtils.randomByBase(column.getBase(), column.getRatio()));
                    break;
                case 2:
                    dev.set(columnName, DataUtils.randomByLimit(column.getMax(), column.getMin()));
                    break;
                case 3:
                    dev.set(columnName, trendData((JSONObject) JSONObject.toJSON(column), size, i));
                    break;
                case 4:
                    dev.set(columnName, DataUtils.formula(column.getFormula(), dev));
                    break;
                case 5:
                    dev.set(columnName, SnowflakeUtil.getId());
                    break;
                default:
                    break;
            }
            if (dataType == 1) {
                if (type == 5) {
                    dev.set(columnName, dev.getLong(columnName));
                } else {
                    dev.set(columnName, dev.getInt(columnName));
                }
            }
            if (dataType == 2) {
                dev.set(columnName, dev.getDouble(columnName));
            }
            if (dataType == 99) {
                dev.set(columnName, new Object());
            }
        }
    }


    /**
     * 设备历史数据
     *
     * @param insertData    生成数据列表
     * @param redundant     冗余数据
     * @param dev           设备信息
     * @param columnConfigs 字段生成规则配置
     * @param begin         开始时间
     * @param end           结束时间
     * @param timeSpace     数据间隔
     */
    private void devHis(List<Record> insertData, Map<String, Object> redundant, Record dev, List<ColumnConfig> columnConfigs, Date begin, Date end, int timeSpace) {
        Long devId = dev.getLong("src_id");
        Date cur = begin;
        cur = DateUtil.offsetMinute(cur, timeSpace);
        Record his;
        while (!cur.after(end)) {
            his = new Record();

            for (ColumnConfig columnConfig : columnConfigs) {
                String columnName = columnConfig.getColumn();
                //0固定值，1基数波动，2上下限值，3一定趋势，4其他值计算，5分片生成
                int type = columnConfig.getType();
                //0字符串 1整数 2浮点数 3时间 99其他
                int dataType = columnConfig.getDataType();

                Matcher matcher = NAME_NUM.matcher(columnName);
                int columnNum = 1;
                String columnPre = columnName;
                if (matcher.matches()) {
                    columnPre = matcher.group(1);
                    columnNum = Integer.parseInt(matcher.group(2));
                }
                if (columnNum > 15) {
                    for (int j = 0; j < columnNum; j++) {
                        columnName = columnPre + (j + 1);
                        if (dataType == 0) {
                            his.set(columnName, ChineseCharUtils.randomChineseChars(2, 10));
                        } else if (dataType == 3) {
                            his.set(columnName, RandomUtil.randomDate(new Date(), DateField.MINUTE, -10, 10));
                        } else {
                            switch (type) {
                                case 0:
                                    his.set(columnName, columnConfig.getBase());
                                    break;
                                case 1:
                                    his.set(columnName, DataUtils.randomByBase(columnConfig.getBase(), columnConfig.getRatio()));
                                    break;
                                case 2:
                                    his.set(columnName, DataUtils.randomByLimit(columnConfig.getMax(), columnConfig.getMin()));
                                    break;
                                case 3:
                                    his.set(columnName, trendData((JSONObject) JSONObject.toJSON(columnConfig), columnNum, j));
                                    break;
                                case 4:
                                    his.set(columnName, DataUtils.formula(columnConfig.getFormula(), his));
                                    break;
                                case 5:
                                    shardingData(columnName, dataType, columnConfig.getSharding(), cur, his, timeSpace);
                                    break;
                                default:
                                    break;
                            }
                            if (dataType == 1) {
                                his.set(columnName, his.getDouble(columnName).intValue());
                            }
                            if (dataType == 99) {
                                his.set(columnName, new Object());
                            }
                        }
                    }
                } else {
                    if (dataType == 0) {
                        his.set(columnName, ChineseCharUtils.randomChineseChars(2, 10));
                    } else if (dataType == 3) {
                        his.set(columnName, RandomUtil.randomDate(new Date(), DateField.MINUTE, -10, 10));
                    } else {
                        switch (type) {
                            case 0:
                                his.set(columnName, columnConfig.getBase());
                                break;
                            case 1:
                                his.set(columnName, DataUtils.randomByBase(columnConfig.getBase(), columnConfig.getRatio()));
                                break;
                            case 2:
                                his.set(columnName, DataUtils.randomByLimit(columnConfig.getMax(), columnConfig.getMin()));
                                break;
                            case 3:
                                long curMs = cur.getTime();
                                long beginMs = DateUtil.beginOfDay(cur).getTime();
                                long endMs = DateUtil.endOfDay(cur).getTime();
                                int size = Math.toIntExact((endMs - beginMs) / ((long) timeSpace * 60 * 1000));
                                int index = Math.toIntExact((curMs - beginMs) / ((long) timeSpace * 60 * 1000));
                                his.set(columnName, trendData((JSONObject) JSONObject.toJSON(columnConfig), size, index));
                                break;
                            case 4:
                                his.set(columnName, DataUtils.formula(columnConfig.getFormula(), his));
                                break;
                            case 5:
                                shardingData(columnName, dataType, columnConfig.getSharding(), cur, his, timeSpace);
                                break;
                            default:
                                break;
                        }
                        if (dataType == 1) {
                            his.set(columnName, his.getDouble(columnName).intValue());
                        }
                        if (dataType == 99) {
                            his.set(columnName, new Object());
                        }
                    }
                }
            }
            his.set("dev_id", devId);
            his.set("time", cur);
            his.setColumns(redundant);
            insertData.add(his);
            cur = DateUtil.offsetMinute(cur, timeSpace);
        }
    }

    /**
     * 趋势数据
     *
     * @param cfg   配置
     * @param size  数据长度
     * @param index 数据位置
     * @return
     */
    private Double trendData(JSONObject cfg, int size, int index) {
        Double value;
        //参考曲线类型
        String refer = cfg.getString("refer");
        //参数
        String param = cfg.getString("param");
        Double max = cfg.getDouble("max");
        Double min = cfg.getDouble("min");

        List<Double> referData = new ArrayList<>();
        switch (refer) {
            case "linear":
                for (int i = 0; i < size; i++) {
                    referData.add(TrendUtils.linear((double) i / size, Integer.parseInt(param)));
                }
                break;
            case "quadratic":
                for (int i = 0; i < size; i++) {
                    referData.add(TrendUtils.quadratic((double) i / size, Integer.parseInt(param)));
                }
                break;
            case "cubic":
                for (int i = 0; i < size; i++) {
                    referData.add(TrendUtils.cubic((double) i / size, Integer.parseInt(param)));
                }
                break;
            case "quartic":
                for (int i = 0; i < size; i++) {
                    referData.add(TrendUtils.quartic((double) i / size, Integer.parseInt(param)));
                }
                break;
            case "quintic":
                for (int i = 0; i < size; i++) {
                    referData.add(TrendUtils.quintic((double) i / size, Integer.parseInt(param)));
                }
                break;
            case "exponential":
                for (int i = 0; i < size; i++) {
                    referData.add(TrendUtils.exponential((double) i / size, Integer.parseInt(param)));
                }
                break;
            case "circular":
                for (int i = 0; i < size; i++) {
                    referData.add(TrendUtils.circular((double) i / size, Integer.parseInt(param)));
                }
                break;
            case "elastic":
                for (int i = 0; i < size; i++) {
                    referData.add(TrendUtils.elastic((double) i / size, Integer.parseInt(param)));
                }
                break;
            case "back":
                for (int i = 0; i < size; i++) {
                    referData.add(TrendUtils.back((double) i / size, Integer.parseInt(param)));
                }
                break;
            case "bounce":
                for (int i = 0; i < size; i++) {
                    referData.add(TrendUtils.bounce((double) i / size, Integer.parseInt(param)));
                }
                break;
            case "sine":
                referData = DataUtils.referSine(size, param);
                break;
            case "circle":
                String[] split = param.split(",");
                if (split.length == 2) {
                    Integer from = Integer.valueOf(split[0]);
                    Integer to = Integer.valueOf(split[1]);
                    referData = DataUtils.referCircle(size, from, to);
                }
                break;
            default:
                break;
        }
        index = Math.min(index, size - 1);
        value = DataUtils.createData(referData, 0.01, max, min, index);
        return value;
    }

    /**
     * 分片数据
     *
     * @param columnName 字段名称
     * @param dataType   数据类型
     * @param sharding   分片配置
     * @param cur        数据时间点
     * @param his        已有数据
     * @param timeSpace  数据间隔
     * @return
     */
    private void shardingData(String columnName, int dataType, String sharding, Date cur, Record his, int timeSpace) {
        JSONArray array = JSONArray.parseArray(sharding);
        if (array == null) {
            his.set(columnName, null);
            return;
        }
        for (int j = 0; j < array.size(); j++) {
            JSONObject shard = array.getJSONObject(j);
            //0固定值，1基数波动，2上下限值，3一定趋势，4其他值计算
            int type = shard.getIntValue("type");
            String shardBegin = shard.getString("begin");
            String shardEnd = shard.getString("end");
            String prefix = DateUtil.format(cur, "yyyy-MM-dd ");
            Date begin = DateUtil.parse(prefix + shardBegin + ":00");
            Date end = DateUtil.parse(prefix + shardEnd + ":00");
            if (DateUtil.isIn(cur, begin, end)) {
                if (dataType == 0) {
                    his.set(columnName, ChineseCharUtils.randomChineseChars(2, 10));
                } else if (dataType == 3) {
                    his.set(columnName, RandomUtil.randomDate(new Date(), DateField.MINUTE, -10, 10));
                } else {
                    switch (type) {
                        case 0:
                            his.set(columnName, shard.getDouble("base"));
                            break;
                        case 1:
                            his.set(columnName, DataUtils.randomByBase(shard.getDouble("base"), shard.getDouble("ratio")));
                            break;
                        case 2:
                            his.set(columnName, DataUtils.randomByLimit(shard.getDouble("max"), shard.getDouble("min")));
                            break;
                        case 3:
                            long curMs = cur.getTime();
                            long beginMs = begin.getTime();
                            long endMs = end.getTime();
                            int size = Math.toIntExact((endMs - beginMs) / ((long) timeSpace * 60 * 1000));
                            int index = Math.toIntExact((curMs - beginMs) / ((long) timeSpace * 60 * 1000));
                            his.set(columnName, trendData(shard, size, index));
                            break;
                        case 4:
                            his.set(columnName, DataUtils.formula(shard.getString("formula"), his));
                            break;
                        default:
                            break;
                    }
                    if (dataType == 1) {
                        his.set(columnName, his.getDouble(columnName).intValue());
                    }
                    if (dataType == 99) {
                        his.set(columnName, new Object());
                    }
                }
            }
        }
    }

    /**
     * 采集点配置数据粗步生成
     *
     * @param devTableNames 设备表
     * @param hisTableNames 数据表
     * @param jobId         关联任务
     * @param machineId     关联管理机
     * @param dataType      消息类型 0遥信 1遥测
     */
    private void syncPoint(List<String> devTableNames, List<String> hisTableNames, Long jobId, Long machineId, int dataType) {
        List<Record> cfgs = new ArrayList<>();
        Date date = new Date();

        List<TableInfo> devTables = tableInfoRepository.findByType(1);
        List<TableInfo> hisTables = tableInfoRepository.findByType(2);

        if (devTableNames != null) {
            devTables = devTables.stream().filter(t -> devTableNames.contains(t.getName())).collect(Collectors.toList());
        }
        if (hisTableNames != null) {
            hisTables = hisTables.stream().filter(t -> hisTableNames.contains(t.getName())).collect(Collectors.toList());
        }

        int i = 0;
        for (TableInfo devTable : devTables) {
            Long devTableId = devTable.getId();
            String devTableName = devTable.getName();

            List<ColumnInfo> devColumns = columnInfoRepository.findByTableId(devTableId);

            TableInfo hisTable = hisTables.stream().filter(r -> devTableId.equals(r.getDevTableId())).findFirst().orElse(new TableInfo());
            Long hisTableId = hisTable.getId();

            List<ColumnInfo> hisColumns = columnInfoRepository.findByTableId(hisTableId);
            List<Record> devs = Db.find("select * from " + devTableName);
            for (Record dev : devs) {
                for (ColumnInfo hisColumn : hisColumns) {
                    String hisColName = hisColumn.getName();
                    Record cfg = new Record();
                    cfg.set("job_id", jobId);
                    cfg.set("comm_machine_id", machineId);
                    cfg.set("modbus_cmd", 3);
                    cfg.set("modbus_addr", i += 2);
                    cfg.set("data_type", dataType);
                    cfg.set("dest_his_table_name", hisTable.getName());
                    cfg.set("dest_his_column_name", hisColName);

                    ColumnInfo rtColumn = devColumns.stream().filter(r -> hisColName.equalsIgnoreCase(r.getName())).findFirst().orElse(null);
                    if (rtColumn != null) {
                        cfg.set("dest_rt_table_name", devTableName);
                        cfg.set("dest_rt_column_name", rtColumn.getName());
                    }
                    cfg.set("dev_table_name", devTableName);
                    cfg.set("dev_id", dev.get("src_id"));
                    cfg.set("interval_seconds", 900);
                    cfg.set("is_use", 1);
                    cfg.set("create_by", "autodata");
                    cfg.set("create_time", date);
                    cfgs.add(cfg);
                }
            }
        }
        if (hisTableNames != null) {
            String names = String.join("','", hisTableNames);
            names = "'" + names + "'";
            Db.delete("delete from cfg_his_modbus_sync where job_id=? and comm_machine_id=? and data_type=? and dest_his_table_name in(" + names + ")", jobId, machineId, dataType);
        }
        Db.batchSave("cfg_his_modbus_sync", cfgs, 1000);
    }

    /**
     * 表定义
     *
     * @param devTableNames 指定设备表集合
     * @param hisTableNames 指定数据表集合
     */
    private void autoTable(List<String> devTableNames, List<String> hisTableNames) {
        List<Object[]> tableList = tableList();
        if (tableList == null) {
            return;
        }
        List<TableInfo> tables = tableList.stream()
                .filter(objects -> {
                    if (objects == null || objects.length != 2 || StrUtil.isEmptyIfStr(objects[0])) {
                        return false;
                    }
                    String tableName = (String) objects[0];
                    tableName = tableName.toLowerCase();
                    if (tableName.startsWith("ad_")) {
                        return false;
                    }
                    if (devTableNames != null && devTableNames.contains(tableName)) {
                        return true;
                    }
                    if (hisTableNames != null && hisTableNames.contains(tableName)) {
                        return true;
                    }
                    return devTableNames == null && hisTableNames == null;
                })
                .map(objects -> {
                    TableInfo tableInfo = new TableInfo();
                    String tableName = (String) objects[0];
                    tableName = tableName.toLowerCase();
                    tableInfo.setName(tableName);
                    tableInfo.setComment((String) objects[1]);
                    tableInfo.setCreateTime(new Date());
                    if (tableName.contains("_his") || tableName.contains("his_")) {
                        tableInfo.setType(1002);
                    }
                    if (devTableNames != null && devTableNames.contains(tableName)) {
                        tableInfo.setType(1);
                    }
                    if (hisTableNames != null && hisTableNames.contains(tableName)) {
                        tableInfo.setType(2);
                    }
                    return tableInfo;
                }).collect(Collectors.toList());

        tables = tables.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(TableInfo::getName))), ArrayList::new));

        List<TableInfo> delTables = tableInfoRepository.findAllByNameIn(tables.stream().map(TableInfo::getName).collect(Collectors.toList()));
        columnInfoRepository.deleteByTableIdIn(delTables.stream().map(TableInfo::getId).collect(Collectors.toList()));
        tableInfoRepository.deleteAllInBatch(delTables);
        tableInfoRepository.saveAll(tables);
        //列定义
        autoColumn(tables);
        //设备表与数据表关联
        connectTable(devTableNames, hisTableNames);
    }

    /**
     * 设备表与数据表关联
     *
     * @param devTableNames 设备表名
     * @param hisTableNames 数据表名
     */
    private void connectTable(List<String> devTableNames, List<String> hisTableNames) {

        List<TableInfo> devTables = tableInfoRepository.findByType(1);
        List<TableInfo> hisTables = tableInfoRepository.findByType(2);

        if (devTableNames != null) {
            devTables = devTables.stream().filter(t -> devTableNames.contains(t.getName())).collect(Collectors.toList());
        }
        if (hisTableNames != null) {
            hisTables = hisTables.stream().filter(t -> hisTableNames.contains(t.getName())).collect(Collectors.toList());
        }

        List<TableInfo> updateTables = new ArrayList<>();
        for (TableInfo hisTable : hisTables) {
            TableInfo dev = devTables.stream()
                    .filter(t -> hisTable.getName().contains(t.getName()))
                    .max(Comparator.comparing(t -> t.getName().length()))
                    .orElse(null);
            if (dev != null) {
                hisTable.setDevTableId(dev.getId());
                updateTables.add(hisTable);
            }
        }
        if (updateTables.size() > 0) {
            tableInfoRepository.saveAll(updateTables);
        }
    }

    /**
     * 列定义
     *
     * @param tables 表列表
     */
    private void autoColumn(List<TableInfo> tables) {
        List<ColumnInfo> columns = new ArrayList<>();
        for (TableInfo table : tables) {
            List<Object[]> columnList = columnList(table.getName());
            if (columnList == null) {
                continue;
            }
            List<ColumnInfo> cols = columnList.stream().filter(objects -> objects != null && objects.length == 3 && !StrUtil.isEmptyIfStr(objects[0]))
                    .map(objects -> {
                        ColumnInfo columnInfo = new ColumnInfo();
                        columnInfo.setTableId(table.getId());
                        String columnName = (String) objects[0];
                        columnName = columnName.toLowerCase();
                        columnInfo.setName(columnName);
                        columnInfo.setComment((String) objects[1]);
                        columnInfo.setCreateTime(new Date());

                        String dt = (String) objects[2];
                        columnInfo.setDataType(dataType(dt));

                        if ("create_by".equals(columnName) || "create_time".equals(columnName) || "update_by".equals(columnName) || "update_time".equals(columnName) || "sys_org_code".equals(columnName) || "id".equals(columnName)) {
                            columnInfo.setType(0);
                        } else if ((columnName.endsWith("_id") && !"src_id".equals(columnName)) || columnName.endsWith("_name")) {
                            columnInfo.setType(2);
//                        } else if (columnName.endsWith("time") || columnName.endsWith("name")) {
//                            columnInfo.setType(3);
                        } else {
                            columnInfo.setType(1);
                        }
                        return columnInfo;
                    }).collect(Collectors.toList());
            columns.addAll(cols);
        }
        columnInfoRepository.saveAll(columns);
    }

    /**
     * 数据类型归类
     *
     * @param dt 数据类型
     * @return 0字符串 1整数 2浮点数 3时间
     */
    private Integer dataType(String dt) {
        dt = dt.toLowerCase();
        if (dt.contains("char") || dt.contains("text")) {
            return 0;
        }
        if (dt.contains("int")) {
            return 1;
        }
        if ("double".equals(dt) || "float".equals(dt) || dt.startsWith("dec") || dt.startsWith("num")) {
            return 2;
        }
        if (dt.contains("date") || dt.contains("time")) {
            return 3;
        }
        return 99;
    }

    /**
     * 获取表信息
     */
    private List<Object[]> tableList() {
        String dbType = CommonUtils.getDbType();
        Query query = null;
        if ("mysql".equals(dbType)) {
            query = entityManager.createNativeQuery("select table_name,table_comment from information_schema.tables where table_schema=(select database())");
        } else if ("oracle".equals(dbType) || "dm".equals(dbType)) {
            query = entityManager.createNativeQuery("select table_name,comments from user_tab_comments ");
        }
        if (query == null) {
            return null;
        }
        return (List<Object[]>) query.getResultList();
    }

    /**
     * 获取表的列信息
     *
     * @param tableName 表名
     */
    private List<Object[]> columnList(String tableName) {
        String dbType = CommonUtils.getDbType();
        Query colQuery = null;
        if ("mysql".equals(dbType)) {
            colQuery = entityManager.createNativeQuery("select column_name, column_comment,data_type from information_schema.columns where table_schema=(select database()) and table_name='" + tableName + "'");
        } else if ("oracle".equals(dbType) || "dm".equals(dbType)) {
//            colQuery = entityManager.createNativeQuery("select column_name,comments from user_col_comments where table_name=upper('" + tableName + "')");
            colQuery = entityManager.createNativeQuery("select utc.column_name, ucc.comments, utc.data_type from user_tab_columns utc left join user_col_comments ucc on ucc.table_name = utc.table_name and ucc.column_name = utc.column_name where utc.table_name=upper('" + tableName + "')");
        }
        if (colQuery == null) {
            return null;
        }
        return (List<Object[]>) colQuery.getResultList();
    }

}
